#!/usr/bin/env python
# -*- coding:utf-8 -*-

"""
Excel(xlsx) Convert JSON
"""

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

import os.path
import ntpath
import optparse
import json
import xlrd
import re

def main(path):
    if not path or not os.path.exists(path) or not os.path.isfile(path):
        raise Exception('Excel Path Not Found')
    workbook = xlrd.open_workbook(path)
    if not workbook:
        raise Exception('Excel File Invalid {%s}' % path)
    # Json地址
    dest_path = '%s.json' % ntpath.splitext(path)[0]
    with open(dest_path, 'w') as fp:
        for sheet in workbook.sheets():
            # 如果列为空
            if sheet.ncols <= 0 or sheet.nrows <= 4:
                continue
            # 读取所有可编辑字段列表
            column = []
            ids = []
            for i in xrange(sheet.ncols):
                cell = sheet.cell(1, i)
                if not cell:
                    continue
                value = cell.value
                if not value:
                    continue
                result = re.findall('\\[(.+?)\\]', value)
                if result:
                    column.append(result[0])
                    ids.append(i)
            # 读取数据
            for j in xrange(sheet.nrows):
                if j < 4:
                    continue
                row = {}
                for i, k in enumerate(ids):
                    cell = sheet.cell(j, k)
                    value = None
                    if cell:
                        value = cell.value
                    if not value:
                        value = ''
                    row[column[i]] = value
                # 写入文件
                fp.write(json.dumps(row))
                fp.write('\n')

    print 'success {%s}' % dest_path

if __name__ == '__main__':
    parser = optparse.OptionParser()
    parser.add_option('-p', '--path', dest='path', help='Excel File Path', default=r'')
    try:
        (options, args) = parser.parse_args()
        main(options.path)
    except Exception as e:
        print e.message
